TRUNCATE TABLE item_customer_behaivor_history;



INSERT INTO item_customer_behaivor_history 
    (customer_id, item_id, profit, profit_star, roi, roi_star, date)
    
    
WITH Averages AS (
    SELECT 
        customer_id,
        item_id,
        AVG(profit) AS avg_profit,
        AVG(roi) AS avg_roi,
        MAX(date) AS last_date
    FROM sales_behaivor_history
    GROUP BY 
        customer_id, 
        item_id
),

Percentiles AS (
    SELECT DISTINCT
        -- Percentiles para profit usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY profit) OVER () AS p_profit_80,

        -- Percentiles para ROI usando datos mensuales
        PERCENTILE_CONT(0.2) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_20,
        PERCENTILE_CONT(0.4) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_40,
        PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_60,
        PERCENTILE_CONT(0.8) WITHIN GROUP (ORDER BY roi) OVER () AS p_roi_80
    FROM sales_behaivor_history
)

SELECT 	
    a.customer_id,
    a.item_id,

    a.avg_profit,
    CASE 
        WHEN a.avg_profit <= p.p_profit_20 THEN 1
        WHEN a.avg_profit <= p.p_profit_40 THEN 2
        WHEN a.avg_profit <= p.p_profit_60 THEN 3
        WHEN a.avg_profit <= p.p_profit_80 THEN 4
        ELSE 5 
    END AS profit_star,

    a.avg_roi,
    CASE 
        WHEN a.avg_roi <= p.p_roi_20 THEN 1
        WHEN a.avg_roi <= p.p_roi_40 THEN 2
        WHEN a.avg_roi <= p.p_roi_60 THEN 3
        WHEN a.avg_roi <= p.p_roi_80 THEN 4
        ELSE 5 
    END AS roi_star,

    a.last_date

FROM Averages a
CROSS JOIN Percentiles p;